import pymysql as ms
from mysql import connector

con = ms.connect(
    host='rm-2vcx83n5f65o240isgo.mysql.cn-chengdu.rds.aliyuncs.com',
    port=3306,
    user='fumingche',
    password='fmc&swufe2021',
    db='imzhuge_db',
    charset='utf8'
)
cursor = con.cursor()

# Number of daily registrations
def login_daily_num(cursor):
    sql = 'select date_format(create_time,"%Y.%m.%d"),count(*) FROM login_user group by date_format(create_time,"%Y.%m.%d")'
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    con.close()
    d_l = dict(data)
    print(d_l)

# Daily Trading number
def trade_daily_num(cursor):
    sql = 'select date_format(trade_time,"%Y.%m.%d"), count(*) FROM market_trade group by date_format(trade_time,"%Y.%m.%d")'
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    cursor.close()
    d_l = dict(data)
    print(d_l)

# Daily Transactions
def trade_daily_fre(cursor):
    sql = 'select date_format(t.trade_time,"%Y.%m.%d"), count(*) FROM market_trade m left join market_trade t on m.user_id=t.user_id where 1=1 group by date_format(t.trade_time,"%Y.%m.%d")'
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    cursor.close()
    d_l = dict(data)
    print(d_l)

# Average number of participants
def activity_avr_num(cursor):
    sql = 'select count(*)/count(DISTINCT activity_id) FROM market_activityuseraccount'
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    con.close()
    a = list(data)
    data = list(map(float, a[0]))
    print(data)

#login_daily_num(cursor)
#trade_daily_num(cursor)
trade_daily_fre(cursor)

